using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace MacViet.DataBaseAPI
{	
	/// <summary>
	/// Description: Concrete Dataprovider for table Account. Provides atomic API functions.
	/// Author:  phong.huynh
	/// Created: 8/12/2010 11:14:20 PM
	/// </summary>
    public class ACCOUNTSDataAPI
	{
		private SQLHelper _dbTool = new SQLHelper();
	
		// ************************************
		// Implements function getByColumnName
		// ************************************
		
		public DataTable getByACC_ID(System.Int32 ACC_ID,string email)
		{
            SqlParameter[] parm={new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                    new SqlParameter(ACCOUNTSDTO._ACC_EMAIL,email)};
			return _dbTool.executeReader("SP_SEL_ACCOUNTS_getByACC_ID",parm);
		}
        public bool Acc_EmailCheck(string email)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_EMAIL, email) };
            DataTable dt = _dbTool.executeReader("SP_ACC_EMAIL_CHECK",parm);
            if (dt != null)
                return true;
            return false;
        }
        public bool AddminLogin(string email,string password)
        {
            password = _dbTool.GetEncryString(password);
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_EMAIL, email),
                                  new SqlParameter(ACCOUNTSDTO._ACC_PASSWORD,password)};
            DataTable dt = _dbTool.executeReader("SP_ADDMIN_LOGIN", parm);
            if (dt != null)
                return true;
            return false;
        }
        public DataTable getAccountByEmail(string email)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_EMAIL,email)};
            return _dbTool.executeReader("sp_getAccountbyEmail",parm);
        }
		
		public DataTable getByACC_NAME(System.String ACC_NAME)
		{
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_NAME,ACC_NAME)};
			return _dbTool.executeReader("SP_SEL_ACCOUNTS_getByACC_NAME",parm);
		}
		
		public DataTable getByACC_ADDRESS(System.String ACC_ADDRESS)
		{
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ADDRESS,ACC_ADDRESS)};
			return _dbTool.executeReader("SP_SEL_ACCOUNTS_getByACC_ADDRESS", parm);
		}
		
		public DataTable getByACC_PHONE(System.String ACC_PHONE)
		{
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_PHONE,ACC_PHONE)};
			return _dbTool.executeReader("SP_SEL_ACCOUNTS_getByACC_PHONE",parm);
		}
		
		public DataTable getByACC_EMAIL(System.String ACC_EMAIL)
		{
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_EMAIL,ACC_EMAIL)};
			return _dbTool.executeReader("SP_SEL_ACCOUNTS_getByACC_EMAIL", ACC_EMAIL);
		}
		
		public DataTable getByACC_PASSWORD(System.String ACC_PASSWORD)
		{
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_PASSWORD,_dbTool.GetEncryString(ACC_PASSWORD))};
			return _dbTool.executeReader("SP_SEL_ACCOUNTS_getByACC_PASSWORD",parm);
		}
		
		public DataTable getByACC_SECURITYCODE(System.String ACC_SECURITYCODE)
		{
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_SECURITYCODE,ACC_SECURITYCODE)};
			return _dbTool.executeReader("SP_SEL_ACCOUNTS_getByACC_SECURITYCODE", parm);
		}
		// *************************************************
		// Implement function getAll/getByDynamicCondition
		// *************************************************

		public DataTable getAll()
		{
			return _dbTool.executeReader("SP_SEL_ACCOUNTS_getAll");
		}

        public bool AccountLogin(string email, string password)
        {
            password = _dbTool.GetEncryString(password);
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_EMAIL,email),
                                    new SqlParameter(ACCOUNTSDTO._ACC_PASSWORD,password)};
            DataTable dt = new DataTable();
            dt = _dbTool.executeReader("sp_Account_Login", parm);             
            if (dt!=null)
                return true;
            return false;
        }

        public bool AccountCheckLogin(string email, string password,string securityCode)
        {
            password = _dbTool.GetEncryString(password);
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_EMAIL,email),
                                    new SqlParameter(ACCOUNTSDTO._ACC_PASSWORD,password),
                                    new SqlParameter(ACCOUNTSDTO._ACC_SECURITYCODE,securityCode)};
            DataTable dt = new DataTable();
            dt = _dbTool.executeReader("sp_AccountLogin", parm);
            if (dt != null)
                return true;
            return false;
        }
        public bool AccountCheckLoginBySecurityCode(string email, string password, string securityCode)
        {
            password = _dbTool.GetEncryString(password);
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_EMAIL,email),
                                    new SqlParameter(ACCOUNTSDTO._ACC_PASSWORD,password),
                                    new SqlParameter(ACCOUNTSDTO._ACC_SECURITYCODE,securityCode)};
            DataTable dt = new DataTable();
            dt = _dbTool.executeReader("sp_AccountLoginbySecurity", parm);
            if (dt != null)
                return true;
            return false;
        }

		// ********************************************************
		// Implement function getByPrimaryKeys / getByForeignKeys
		// In case table have composite primary/foreign keys
		// ********************************************************

		public DataTable getByPrimaryKeys(System.Int32 ACC_ID )
		{
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID)};
			return _dbTool.executeReader("SP_SEL_ACCOUNTS_getByPrimaryKeys", parm);
		}
		public  bool insertACCOUNTS(System.String ACC_NAME,
            System.String ACC_ADDRESS, System.String ACC_PHONE, System.String ACC_EMAIL, 
            System.String ACC_PASSWORD, System.String ACC_SECURITYCODE, System.DateTime ACC_STARTDATE, 
            System.DateTime ACC_ENDDATE, System.Boolean ACC_STATUS, System.Boolean ACC_AUTOPAYMENT)
		{
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,1),
                                  new SqlParameter(ACCOUNTSDTO._ACC_NAME,ACC_NAME),
                                  new SqlParameter(ACCOUNTSDTO._ACC_ADDRESS,ACC_ADDRESS),
                                  new SqlParameter(ACCOUNTSDTO._ACC_PHONE,ACC_PHONE),
                                  new SqlParameter(ACCOUNTSDTO._ACC_EMAIL,ACC_EMAIL),
                                  new SqlParameter(ACCOUNTSDTO._ACC_PASSWORD,_dbTool.GetEncryString(ACC_PASSWORD)),
                                  new SqlParameter(ACCOUNTSDTO._ACC_SECURITYCODE,ACC_SECURITYCODE),
                                  new SqlParameter(ACCOUNTSDTO._ACC_STARTDATE,ACC_STARTDATE),
                                  new SqlParameter(ACCOUNTSDTO._ACC_ENDDATE,ACC_ENDDATE),
                                  new SqlParameter(ACCOUNTSDTO._ACC_STATUS,ACC_STATUS),
                                  new SqlParameter(ACCOUNTSDTO._ACC_AUTOPAYMENT,ACC_AUTOPAYMENT)};
			int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_ACCOUNTS_values",parm);
			return (effectedRows == 1);
		}

        public bool updateAccountpass(int id, String newPass, string currentPass)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,id),                                 
                                  new SqlParameter(ACCOUNTSDTO._ACC_PASSWORD,_dbTool.GetEncryString(newPass)),
                                  new SqlParameter("@OLD_PASSWORD",_dbTool.GetEncryString(currentPass))};

            int affectRows = _dbTool.ExecuteNoneQuery(StoreSQl.SP_UDP_Accounts_password, parm);

            return affectRows == 1;
        }
        public bool HighScore_Insert(System.String userName, System.String country, System.Double highScore)
        {
            SqlParameter[] parm = { new SqlParameter("@USERNAME",userName),                              
                                  new SqlParameter("@COUNTRY",country),
                                  new SqlParameter("@HIGHSCORE",highScore)};

            int affectRows = _dbTool.ExecuteNoneQuery("SP_INS_HIGHSCORE", parm);

            return affectRows == 1;
        }
        public bool HighScore_UPD(System.String userName, System.String country, System.Double highScore)
        {
            SqlParameter[] parm = { new SqlParameter("@USERNAME",userName),                              
                                  new SqlParameter("@COUNTRY",country),
                                  new SqlParameter("@HIGHSCORE",highScore)};

            int affectRows = _dbTool.ExecuteNoneQuery("SP_UPD_HIGHSCORE", parm);

            return affectRows == 1;
        }
        public bool HighScore_CHK(System.String userName, System.String country)
        {
            SqlParameter[] parm = { new SqlParameter("@USERNAME",userName),                              
                                  new SqlParameter("@COUNTRY",country)};
            DataTable dt = new DataTable();
            dt = _dbTool.executeReader("SP_CHK_HIGHSCORE", parm);
            if (dt != null)
                return true;
            return false;

        }

        public DataTable HighScore_GetTop()
        {            
            DataTable dt = new DataTable();
            dt = _dbTool.executeReader("SP_GET_TOP_HIGHSCORE");
            if (dt != null)
                return dt;
            return null;

        }
        ///<summary>		
		/// Thu+.c hie^.n ca^u le^.nh update du+.a tre^n kho'a chi'nh
		///</summary>

		public  bool updateACCOUNTS(System.Int32 ACC_ID , System.String ACC_NAME, 
            System.String ACC_ADDRESS, System.String ACC_PHONE, System.String ACC_EMAIL,
            System.String ACC_PASSWORD, System.String ACC_SECURITYCODE, System.DateTime ACC_STARTDATE, 
            System.DateTime ACC_ENDDATE, System.Boolean ACC_STATUS, System.Boolean ACC_AUTOPAYMENT)
		{
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                  new SqlParameter(ACCOUNTSDTO._ACC_NAME,ACC_NAME),
                                  new SqlParameter(ACCOUNTSDTO._ACC_ADDRESS,ACC_ADDRESS),
                                  new SqlParameter(ACCOUNTSDTO._ACC_PHONE,ACC_PHONE),
                                  new SqlParameter(ACCOUNTSDTO._ACC_EMAIL,ACC_EMAIL),
                                  new SqlParameter(ACCOUNTSDTO._ACC_PASSWORD,_dbTool.GetEncryString(ACC_PASSWORD)),
                                  new SqlParameter(ACCOUNTSDTO._ACC_SECURITYCODE,ACC_SECURITYCODE),
                                  new SqlParameter(ACCOUNTSDTO._ACC_STARTDATE,ACC_STARTDATE),
                                  new SqlParameter(ACCOUNTSDTO._ACC_ENDDATE,ACC_ENDDATE),
                                  new SqlParameter(ACCOUNTSDTO._ACC_STATUS,ACC_STATUS),
                                  new SqlParameter(ACCOUNTSDTO._ACC_AUTOPAYMENT,ACC_AUTOPAYMENT)};
			int affectRows = _dbTool.ExecuteNoneQuery("SP_UDP_ACCOUNTS_update",parm);

			return affectRows == 1;
		}
        		

		// ********************************************************************************
		// Implementation of deleteAll/deteleByForeignKeys/deleteByPrimaryKeys function		
		// ********************************************************************************

		// return number of deleted rows		
		public int deleteAll()
		{           
			return _dbTool.ExecuteNoneQuery("SP_DEL_ACCOUNTS_deleteAll");
		}

		// return number of deleted rows
		public int deleteByPrimaryKeys(System.Int32 ACC_ID )
		{
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID)};
			return _dbTool.ExecuteNoneQuery("SP_DEL_ACCOUNTS_deleteByPrimaryKeys", parm);
		}
		
	}
}